package org.omlite.domain.db;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.context.FacesContext;

public class SQLiteConnector implements Serializable
{

    private static final Logger log = Logger.getLogger(SQLiteConnector.class.getName());

    private Statement stmt;
    private Connection conn;
    private final String JDBC = "jdbc:sqlite:",
            DRIVER = "org.sqlite.JDBC",
            dbLocation;
    private StringBuilder connectionString;
    private boolean autoConnect = true,
            showConsoleOutput,
            isConnected;

    //<editor-fold defaultstate="collapsed" desc="Gettors 'n Settors...">
    /**
     * Default is 'true'
     *
     * @param showConsoleOutput
     */
    public void setShowConsoleOutput(boolean showConsoleOutput)
    {
        this.showConsoleOutput = showConsoleOutput;
    }
    //</editor-fold>

    /**
     * Looking for the db in the WEB-INF dir.
     *
     * @param dbFileName
     */
    public SQLiteConnector(String dbFileName)
    {
        StringBuilder p = new StringBuilder(FacesContext.getCurrentInstance().getExternalContext().getRealPath("/"));
        p.append("/WEB-INF/");
        p.append(dbFileName);
        dbLocation = p.toString();
    }

    /**
     * Connecting using the variables above. Not nescessary if the 'autologin'
     * variable = true; which is the case by default.
     */
    public void Connect()
    {
        if (isConnected)
        {
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "Already connected to DB");
            }
        } else
        {
            try
            {
                connectionString = new StringBuilder(JDBC);
                connectionString.append(dbLocation);
                Class.forName(DRIVER);
                conn = DriverManager.getConnection(connectionString.toString());
                isConnected = true;
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, "Database connection established with: ");
                    log.log(Level.INFO, connectionString.toString());
                }
            } catch (ClassNotFoundException | SQLException e0)
            {
                isConnected = false;
                log.log(Level.INFO, e0.toString());
            }
        }
    }

    /**
     * Disconnecting returning possible errors... Use before your app. is closed
     */
    public void Disconnect()
    {
        if (!isConnected)
        {
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "\nDB already disconnected");
            }
        } else
        {
            try
            {
                conn.close();
                stmt.close();
            } catch (Exception e0)
            {
                isConnected = true;
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, "DB failed to disconnect");
                }
            }
            isConnected = false;
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "\nDB disconnected");
            }
        }
    }

    /**
     * Use this method for Select Queries only...
     *
     * @param sql
     * @return
     */
    public ResultSet ExecuteSelectStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        ResultSet rs = null;
        if (isConnected)
        {
            try
            {
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, "Statement\n");
                    log.log(Level.INFO, sql);
                    log.log(Level.INFO, "\nsuccessfully executed.");
                }
                return rs;
            } catch (Exception e0)
            {
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, e0.toString());
                }
                Disconnect();
                return rs;
            }
        } else
        {
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "Not connected to DB");
            }
            return rs;
        }
    }

    /**
     * Use this method for Update statements....
     *
     * @param sql
     */
    public void ExecuteUpdateStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        if (isConnected)
        {
            StringBuilder msg = new StringBuilder();
            msg.append("Statement:\n\n");
            msg.append(sql);
            msg.append("\n\nSuccessfully Executed!");
            try
            {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, msg.toString());
                }
                if (autoConnect)
                {
                    Disconnect();
                }
            } catch (Exception e0)
            {
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, e0.toString());
                }
                Disconnect();
            }
        } else
        {
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "Not connected to DB");
            }
        }
    }

    /**
     * Use this method for Insert statements....
     *
     * @param sql
     */
    public void ExecuteInsertStatement(String sql)
    {
        if (autoConnect)
        {
            Connect();
        }
        if (isConnected)
        {
            StringBuilder msg = new StringBuilder();
            msg.append("Statement:\n\n");
            msg.append(sql);
            msg.append("\n\nSuccessfully Executed!");
            try
            {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, msg.toString());
                }
                if (autoConnect)
                {
                    Disconnect();
                }
            } catch (Exception e0)
            {
                if (showConsoleOutput)
                {
                    log.log(Level.INFO, e0.toString());
                }
                Disconnect();
            }
        } else
        {
            if (showConsoleOutput)
            {
                log.log(Level.INFO, "Not connected to DB");
            }
        }
    }

}
